package tr.com.argela.tap.mot.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import tr.com.argela.tap.mot.logger.LoggerFactory;
import tr.com.argela.tap.mot.models.TestCase;
import tr.com.argela.tap.mot.models.TestPoint;
import tr.com.argela.tap.mot.models.User;

/**
 * @author arge
 */
public class TestCaseDao {

    private static Logger logger = LoggerFactory.getLogger("servlets");

    public TestCaseDao() {

    }

    public List<TestCase> seetcase(Long test_point_id) {
        TestCase tcase = null;
        PreparedStatement stmt = null;
        List<TestCase> tcaselist = new ArrayList<TestCase>();
        String sql = "select * from test_case where test_point_id = ? ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, test_point_id);
            rs = stmt.executeQuery();

            while (rs.next()) {
                tcase = new TestCase();
                tcase.setId(rs.getLong("id"));
                tcase.setName(rs.getString("name"));
                tcase.setBreq(rs.getString("breq"));
                TestPoint point = new TestPoint();
                point.setId(rs.getLong("test_point_id"));
                tcase.setTestPoint(point);
                tcase.setPrecondition(rs.getString("precondition"));
                tcase.setDescription(rs.getString("test_definition"));
                tcase.setExpectedRes(rs.getString("expected_result"));
                tcase.setCheck(rs.getString("check_item"));
                tcase.setResult(rs.getString("result"));
                tcase.setNotes(rs.getString("notes"));
                tcase.setArtno(rs.getString("art_no"));
                User user = new User();
                user.setId(rs.getLong("user_id"));
                tcase.setUser(user);
                tcaselist.add(tcase);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (tcase == null) {
            System.out.println("Test Case is null: ");
        }
        return tcaselist;

    }

    public List<TestCase> seetcase2(Long user_id) {
        TestCase tcase = null;
        PreparedStatement stmt = null;
        List<TestCase> tcaselist = new ArrayList<TestCase>();
        String sql = "select * from test_case where user_id = ? ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, user_id);
            rs = stmt.executeQuery();

            while (rs.next()) {
                tcase = new TestCase();
                tcase.setId(rs.getLong("id"));
                tcase.setName(rs.getString("name"));
                tcase.setBreq(rs.getString("breq"));
                TestPoint point = new TestPoint();
                point.setId(rs.getLong("test_point_id"));
                tcase.setTestPoint(point);
                tcase.setPrecondition(rs.getString("precondition"));
                tcase.setDescription(rs.getString("test_definition"));
                tcase.setExpectedRes(rs.getString("expected_result"));
                tcase.setCheck(rs.getString("check_item"));
                tcase.setResult(rs.getString("result"));
                tcase.setNotes(rs.getString("notes"));
                tcase.setArtno(rs.getString("art_no"));
                User user = new User();
                user.setId(rs.getLong("user_id"));
                tcase.setUser(user);
                tcaselist.add(tcase);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (tcase == null) {
            System.out.println("Test Case is null: ");
        }
        return tcaselist;

    }

    public TestCase checktcase(Long id) {
        TestCase tcase = null;
        PreparedStatement stmt = null;
        String sql = "select tc.id as test_case_id, tc.name as test_case_name, tc.breq, tp.id as test_point_id,"
                + " tp.name as test_point_name, tc.precondition, tc.test_definition, tc.expected_result,"
                + "tc.check_item, tc.result, tc.notes, tc.art_no, u.name as username, u.id as user_id "
                + " from test_case tc, test_point tp, user u where tc.id= ? and u.id=tc.user_id and tp.id=tc.test_point_id ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            tcase = new TestCase();
            while (rs.next()) {
                tcase.setId(rs.getLong("test_case_id"));
                tcase.setName(rs.getString("test_case_name"));
                tcase.setBreq(rs.getString("breq"));
                TestPoint point = new TestPoint();
                point.setId(rs.getLong("test_point_id"));
                point.setName(rs.getString("test_point_name"));
                tcase.setTestPoint(point);
                tcase.setPrecondition(rs.getString("precondition"));
                tcase.setDescription(rs.getString("test_definition"));
                tcase.setExpectedRes(rs.getString("expected_result"));
                tcase.setCheck(rs.getString("check_item"));
                tcase.setResult(rs.getString("result"));
                tcase.setNotes(rs.getString("notes"));
                tcase.setArtno(rs.getString("art_no"));
                User user = new User();
                user.setName(rs.getString("username"));
                user.setId(rs.getLong("user_id"));
                tcase.setUser(user);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (tcase == null) {
            System.out.println("test case is null: ");
        }
        return tcase;

    }

    public int addTestCase(DbManager dbManager, String name, String breq, TestPoint testPoint, String precondition, String description,
            String expectedRes, String check, String notes, String artno, User user) {

        PreparedStatement stmt = null;
        int effectedRows = 0;

        String sql = "insert into test_case (name,breq,test_point_id,precondition,test_definition,expected_result,check_item,result,notes,art_no,user_id) values (?,?,?,?,?,?,?,'NA',?,?,?)";
        System.out.println("Query: " + sql);
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setString(2, breq);
            stmt.setLong(3, testPoint.getId());
            stmt.setString(4, precondition);
            stmt.setString(5, description);
            stmt.setString(6, expectedRes);
            stmt.setString(7, check);

            stmt.setString(8, notes);
            stmt.setString(9, artno);
            stmt.setLong(10, user.getId());

            effectedRows = stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }

        return effectedRows;
    }

    public int UpdateTestCase(DbManager dbManager, String name, String breq, TestPoint testPoint, String precondition, String description,
            String expectedRes, String check, User user, Long caseId) {

        PreparedStatement stmt = null;
        int effectedRows = 0;

        String sql = "update test_case set name = ? , breq = ? , test_point_id = ? , precondition = ? , test_definition = ? , expected_result = ? , check_item = ? , user_id = ? where id = ?";
        System.out.println("Query: " + sql);
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setString(2, breq);
            stmt.setLong(3, testPoint.getId());
            stmt.setString(4, precondition);
            stmt.setString(5, description);
            stmt.setString(6, expectedRes);
            stmt.setString(7, check);
            stmt.setLong(8, user.getId());
            stmt.setLong(9, caseId);

            effectedRows = stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }

        return effectedRows;
    }

    public int executeTestCase(DbManager dbManager, String result, String notes, String artno, String name, Long caseId) {

        PreparedStatement stmt = null;
        int effectedRows = 0;

        String sql = "update test_case set  result = ? , notes = ? , art_no = ?, name = ?  where id = ?";
        System.out.println("Query: " + sql);
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setString(1, result);
            stmt.setString(2, notes);
            stmt.setString(3, artno);
            stmt.setString(4, name);
            stmt.setLong(5, caseId);

            effectedRows = stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }

        return effectedRows;
    }

}